package com.houdoudou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.houdoudou.bean.Customer;

public class CustomerDao {

	 Connection conn=null;

	public CustomerDao(Connection conn) {
		super();
		this.conn = conn;
	}
	 
	
	 public void create(Customer customer) throws SQLException{
			
		   PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
		   String sql=null;
		   
		   try{
			   sql = "insert  into customer (secret,paySecret,money,spareTelnum,telnum) "
		       			+ "values (?,?,?,?,?)";
			  
		       	pstm=conn.prepareStatement(sql);
		    	pstm.setString(1, customer.getSecret());
		       	pstm.setString(2, customer.getPaySecret());	       
		     	pstm.setDouble(3, customer.getMoney());
		     	pstm.setString(4, customer.getSpareTelnum());
		     	pstm.setString(5, customer.getTelnum());
		       	pstm.executeUpdate();
		   }finally{
			   ConnFactory.closeStatment(pstm, pstm, rs);
		   }
	     }		
			
	   
	   public List<Customer> search(String sql) throws SQLException {
		   List<Customer> customers= new ArrayList<Customer>();
		   PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	

	        try {
	        		        	
				stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
				rs=stm.executeQuery(sql);
				while(rs.next()){
					Customer customer= new Customer();
					customer.setTelnum(rs.getString("telnum"));
					customer.setSecret(rs.getString("secret"));
					customer.setPaySecret(rs.getString("paySecret"));
					customer.setMoney(rs.getDouble("money"));
					customer.setSpareTelnum(rs.getString("spareTelnum"));
					customers.add(customer);
				}
	        }finally{
	        	ConnFactory.closeStatment(pstm, pstm, rs);
	        }
		   
		   return customers;
	   }
	   
	   public Customer selectById(String telnum) throws Exception{
		   String sql="select * from customer where telnum = "+ telnum+" ";
		   List<Customer> customers= search(sql);
		   Customer customerExist = null;
		   if(customers.size()>0){ customerExist= customers.get(0); }
		  
		   return customerExist;
	   }
	   
	   public  int update(Customer customer) throws SQLException{
		   int result = 0;
		   PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
		   String sql=null;
	        try {
	        	sql = "update customer set secret=?,paySecret=?,money=?,spareTelnum=?   where telnum=?";
	        
	        	pstm=conn.prepareStatement(sql);
	        	pstm.setString(1, customer.getSecret());
		       	pstm.setString(2, customer.getPaySecret());	       
		     	pstm.setDouble(3, customer.getMoney());
		     	pstm.setString(4, customer.getSpareTelnum());
		     	pstm.setString(5, customer.getTelnum());
		     
		    	result= pstm.executeUpdate();
	        	}finally{
	        		ConnFactory.closeStatment(pstm, pstm, rs);
	        	}
	        
		   return result;
	   }
	   
	   public  void delete(Customer customer) throws SQLException{
		   PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
		   String sql=null;
	        try {
	        	sql = "delete  from customer where telnum="+customer.getTelnum()+"";		        	
				stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
				stm.executeUpdate(sql);		
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}		
	   }
	
	   
	
}
